package dao;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.ResultSet;

import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.mysql.jdbc.PreparedStatement;

import bean.Employee;
import exception.DAOException;

public class EmployeeDAOImpl extends DAO implements EmployeeDAO {

	@Override
	public List<Employee> viewEmployees() throws DAOException {
		Statement stmt = null;
		Connection connection = null;
		List<Employee> lstEmployee = new ArrayList<Employee>();
		try {
			connection = getConnection();
			if (connection != null) {
				//String SQL = "SELECT person.first_name, person.last_name, person.id, employee.work_description, employee.role, employee.hire_date FROM ars1.person, ars1.employee WHERE person.id = employee.id;";
				String SQL = "SELECT * FROM ars1.person p, ars1.employee e where p.id = e.id;";
				stmt = connection.createStatement();
				stmt.setFetchSize(50);
				ResultSet resultSet = stmt.executeQuery(SQL);
				while(resultSet.next()){
					Employee empl = new Employee();
					empl.setId(resultSet.getLong("id"));
					empl.setFirstName(resultSet.getString("first_name"));
						empl.setLastName(resultSet.getString("last_name"));
					empl.setFirstName(resultSet.getString("first_name"));
					empl.setLastName(resultSet.getString("last_name"));
					empl.setUserName(resultSet.getString("username"));
					empl.setPassword(resultSet.getString("password"));
					empl.setAddressLine1(resultSet.getString("address_line1"));
					empl.setAddressLine2(resultSet.getString("address_line2"));
					empl.setCity(resultSet.getString("city"));
					empl.setState(resultSet.getString("state"));
					//empl.setZip(resultSet.getLong("zip"));
					//empl.setDateOfBirth(resultSet.getString("dob"));
					empl.setWorkDescription(resultSet.getString("work_description"));
					empl.setHireDate(resultSet.getString("hire_date"));
					empl.setRole(resultSet.getString("role"));
					empl.setPosition(resultSet.getString("position"));
					
					lstEmployee.add(empl);
				}

			}

		} catch (SQLException e) {
			e.printStackTrace();
		} //Very important step. Never miss out on this!
		finally {
			try{
				stmt.close();
				connection.close();
			}catch(Exception ex){
				
			}	
		}
		return lstEmployee;
	}
	
	public List<Employee> searchEmployees(String firstName, String lastName, long id, String workDescription)throws DAOException, IOException
	{
		Statement stmt = null;
		Connection connection = null;
		List<Employee> lstEmployee = new ArrayList<Employee>();
		//ask user for AND search | OR Search
		System.out.println("Enter AND or OR");
		BufferedReader br = new BufferedReader(new InputStreamReader(System.in));
		String input = br.readLine();
		if (input.equals("AND")){
		try {
			connection = getConnection();
			if (connection != null) {
				String SQL = "SELECT person.first_name, person.last_name, person.id, employee.work_description, employee.role FROM ars1.person, ars1.employee WHERE person.id = "+id+", person.id = employee.id, person;";
				stmt = connection.createStatement();
				stmt.setFetchSize(50);
				ResultSet resultSet = stmt.executeQuery(SQL);
				while(resultSet.next()){
					Employee empl = new Employee();
					empl.setId(resultSet.getLong("id"));
					empl.setFirstName(resultSet.getString("first_name"));
					empl.setLastName(resultSet.getString("last_name"));
					empl.setWorkDescription(resultSet.getString("work_description"));
					empl.setRole(resultSet.getString("role"));
					lstEmployee.add(empl);
				}

			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		//Very important step. Never miss out on this!
		finally {
			try{
				stmt.close();
				connection.close();
			}catch(Exception ex){
				ex.printStackTrace();
			}
		}
		}
		return lstEmployee;
	}
	
	//This is just s test main to test the methods in this class.
	//May be removed in future
	public static void main(String[] args) throws DAOException {
		EmployeeDAO e = new EmployeeDAOImpl();

		boolean flg=true;

		System.out.println(flg);




		List<Employee> e1 = e.viewEmployees();
		System.out.println(e1.get(0).getId());
		System.out.println(e1.get(0).getFirstName()+ " " + e1.get(0).getLastName());
		System.out.println(e1.get(0).getWorkDescription()+ "  " + e1.get(0).getRole());                                 

//		List<Employee> e1 = e.viewEmployees();
//		System.out.println(e1.get(0).getId());
//		System.out.println(e1.get(0).getFirstName());
//		System.out.println(e1.get(0).getWorkDescription());                                 


	}
	
	
	public void createEmployee(String firstName, String lastName, String userName,
			String password, String addressLine1,String addressLine2, String city, 
			String state, String dateOfBirth, long id, String type,
			String workDescription, String hireDate, String position, String role, 
			long created_by, String created_on , long updated_by,
			String updated_on, long zip ) throws DAOException {
		
		boolean insflag=true;
		Statement stmt = null;
		Connection connection = null;
		try {
			connection = getConnection();
			stmt=connection.createStatement();
			if (connection != null) {
				
			/*	String SQL1 = "insert into person values ('" + firstName + "','" + lastName + "','" + userName +
				"','" + password  + "','" + addressLine1 + "','" + addressLine2 + "','" + city + "','" 
				+ state + "'," + id + ",'" + type + "'," + created_by + "," + created_on + "," +
				updated_by + "," + updated_on + "," + dateOfBirth + " )";
				
				String SQL2 = "insert into employee values ( "+ id + ",'" + workDescription + "'," + 
				hireDate + ",'" + role + "','" + position + ")";  */
		
				stmt.addBatch("insert into ars1.person (first_name, last_name, username, password, address_line1," +
						" address_line2, city, state, type, created_by, created_on, updated_by, updated_on, dob, zip ) values ('" + firstName + "','" + lastName + "','" + userName +
						"','" + password  + "','" + addressLine1 + "','" + addressLine2 + "','" + city + "','" 
						+ state + "','" + type + "'," + created_by + ",now()," +
						updated_by + ",now(),'" + dateOfBirth + "', "+zip+")" );
		//		stmt.executeUpdate();
				System.out.println("insert into ars1.employee (id, work_description, hire_date, role, position) values select max(id), '"+ workDescription + "','" + 
						hireDate + "','" + role + "','" + position + "' from ars1.person)");
				
				stmt.addBatch("insert into ars1.employee (id, work_description, hire_date, role, position)  select max(id), '"+ workDescription + "','" + 
				hireDate + "','" + role + "','" + position + "' from ars1.person");
				stmt.executeBatch();
	
			} 
			}catch (Exception ex){
				insflag=false;
				System.out.println(ex.toString());}

			finally {
				try {
					stmt.close();
					connection.close();
				} catch (Exception ex) {

				}
			}
		//return insflag;
	}

	public void deleteEmployee(long id){
		Statement stmt = null;
		Connection connection = null;
		try {
			connection = getConnection();
			if (connection != null) {
				stmt=connection.createStatement();
				stmt.addBatch("delete from employee where id=" + id );
				//stmt.executeUpdate();
				
				stmt.addBatch("delete from person where id=" + id );
				stmt.executeBatch();
				//System.out.println(stmt);
			} 
			}catch (Exception ex){System.out.println(ex.toString());}

			finally {
				try {
					stmt.close();
					connection.close();
				} catch (Exception ex) {

				}
			}
	}
	
	public void updateEmployee(String firstName, String lastName, String userName,
			String password, String addressLine1,String addressLine2, String city, 
			String state, String dateOfBirth, long id, String type,
			String workDescription, String hireDate, String position, String role, 
			long created_by, String created_on , long updated_by,
			String updated_on, long zip)throws DAOException{

		Statement stmt = null;
		Connection connection = null;
	
		try {
			connection = getConnection();
			stmt=connection.createStatement();
			if (connection != null) {
				
				stmt.addBatch("update person set first_name='"+ firstName + "', last_name= '" 
						+ lastName + "', username='" + userName + "', password='" + password + "', address_line1= '"
						+ addressLine1 + "', address_line2='" + addressLine2 + "', city='" + city + "', state='" 
						+ state + "', updated_by=" + updated_by + ", updated_on=now(), zip="+zip+" where id=" 
						+ id);
			//	stmt.executeUpdate();
			
				stmt.addBatch("update employee set work_description='" + workDescription + 
						"', hire_date='" + hireDate + "', role='" + role + "', position='" + position + "' where id=" + id );
				stmt.executeBatch();
				System.out.println("update employee set work_description='" + workDescription + 
						"', hire_date='" + hireDate + "', role='" + role + "', position='" + position + "' where id=" + id );
			} 
			}catch (Exception ex){System.out.println(ex.toString());}
			
			finally {
				try {
					stmt.close();
					connection.close();
				} catch (Exception ex) {

				}
			}

	}

	@Override
	public Employee getEmployeeDetails(long id) throws DAOException {
		Statement stmt = null;
		Connection connection = null;
		Employee empl = new Employee();
		try {
			connection = getConnection();
			if (connection != null) {
				String SQL = "SELECT * FROM ars1.person p, ars1.employee e where p.id = e.id and p.id="+id;
				stmt = connection.createStatement();
				stmt.setFetchSize(50);
				ResultSet resultSet = stmt.executeQuery(SQL);
				while(resultSet.next()){
					empl.setId(resultSet.getLong("id"));
					empl.setFirstName(resultSet.getString("first_name"));
					empl.setLastName(resultSet.getString("last_name"));
					empl.setUserName(resultSet.getString("username"));
					empl.setAddressLine1(resultSet.getString("address_line1"));
					empl.setAddressLine2(resultSet.getString("address_line2"));
					empl.setCity(resultSet.getString("city"));
					empl.setState(resultSet.getString("state"));
					//added by sandhya - these fields are also added in the db. check in script file
					empl.setZip(resultSet.getLong("zip"));
					empl.setDateOfBirth(resultSet.getString("dob"));
					empl.setWorkDescription(resultSet.getString("work_description"));
					empl.setHireDate(resultSet.getString("hire_date"));
					empl.setRole(resultSet.getString("role"));
					empl.setPosition(resultSet.getString("position"));
					empl.setPassword(resultSet.getString("password"));
					
					
				}

			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} //Very important step. Never miss out on this!
		finally {
			try{
				stmt.close();
				connection.close();
			}catch(Exception ex){
				
			}	
		}
		return empl;
	}
	
	
	public List<Employee> searchEmployeeByAttr(List<String> tbl, List<String>paramName, List<String>paramValue) throws DAOException { 

        Statement stmt = null;

        Connection connection = null;

        List<Employee> lstEmployee = new ArrayList<Employee>();

        try {

              connection = getConnection();

              if (connection != null) {

                    for(int indx=0;indx<paramName.size();indx++){

                    String SQL = "select * from " + tbl.get(indx) + " where " + paramName.get(indx) + " = \"" + paramValue.get(indx) + "\";";

                    System.out.println(SQL);

                    stmt = connection.createStatement();

                    stmt.setFetchSize(50);

                    ResultSet resultSet = stmt.executeQuery(SQL);

                    while(resultSet.next()){

                          long id = resultSet.getLong("id");

                          System.out.println(id);

                          String SQL1 = "SELECT * FROM ars1.person p, ars1.employee e where p.id = e.id and p.id=" + id +";" ;

                          System.out.println(SQL1);

                          stmt = connection.createStatement();

                          stmt.setFetchSize(50);

                          ResultSet resultSet1 = stmt.executeQuery(SQL1);

                          while(resultSet1.next()){

                                Employee empl = new Employee();

                                empl.setId(resultSet1.getLong("id"));

                                empl.setFirstName(resultSet1.getString("first_name"));

                                empl.setLastName(resultSet1.getString("last_name"));

                                empl.setUserName(resultSet1.getString("username"));

                                empl.setPassword(resultSet1.getString("password"));

                                empl.setAddressLine1(resultSet1.getString("address_line1"));

                                empl.setAddressLine2(resultSet1.getString("address_line2"));

                                empl.setCity(resultSet1.getString("city"));

                                empl.setState(resultSet1.getString("state"));

                                //empl.setZip(resultSet.getLong("zip"));

                                //empl.setDateOfBirth(resultSet.getString("dob"));

                                empl.setWorkDescription(resultSet1.getString("work_description"));

                                empl.setHireDate(resultSet1.getString("hire_date"));

                                empl.setRole(resultSet1.getString("role"));

                                empl.setPosition(resultSet1.getString("position"));   

                                lstEmployee.add(empl);

                          }

                          }

                    }

              }

              } catch (SQLException e) {

                    // TODO Auto-generated catch block

                    e.printStackTrace();

              } //Very important step. 

        finally {

              try{

                    stmt.close();

                    connection.close();

              }catch(Exception ex){ 

              } 

        }

        return lstEmployee;

  }
}
